Relational Database Design and Modeling for Software Engineers
Rate this course
Ask Author
Contribute
Back to course home

0% completed

Vote For New Content
Introduction
Course Overview
Before the Advent of Database
Fundamental Concepts
What is a Database?
Database management systems (DBMS)
Overview of Relational Databases
Relational Vs. Non-Relational Databases
Data Modeling
Introduction to Data Modeling
Levels of Data Modeling
Data Modeling Process
Best Practices in Data Modeling
Quiz
Entity-Relationship (ER) Data Modeling
Introduction to ER Models
Entities, Attributes, and Relationships
Cardinality and Participation
Attributes of Relationship Types
Weak Entity Types
Creating an ER Diagram for Employee Management System
Quiz
Introduction to the Relational Model
Relations, Tuples, and Attributes
Keys in Relational Databases
Relational Integrity Constraints
Converting ER Diagrams to Relational Model
Best Practices for ER Diagrams to Relational Models
Quiz
Introduction to Functional Dependency
Types of Functional Dependencies
Inference Rules for Functional Dependencies
Closure of Attribute Sets
Quiz
Introduction to Normalization
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Boyce-Codd Normal Form (BCNF)
Higher Normal Forms (4NF, 5NF)
Exercise 1
Solution to Exercise 1
Exercise 2
Solution to Exercise 2
Designing a Hospital Management System
Designing a Hotel Management System
Designing a Bank Management System
Designing an Instagram
Designing an E-commerce Platform
Designing an Online Food Delivery System
Cardinality and Participation

In the ER model, Degree of Relationship, cardinality and participation are essential concepts that describe the constraints on relationships between entities. These concepts specify how many instances of one entity can or must be associated with instances of another entity, helping to clarify the nature of interactions between data elements.

Degree of Relationship

The degree of a relationship in an ER model indicates the number of entity types involved in a relationship. It provides information on the complexity of interactions between entities.

Unary (Degree 1)

A unary relationship (also known as a recursive relationship) involves only one entity type. An entity relates to itself, meaning instances of the same entity are associated with each other.

  • Example: In an Employee entity, an employee can supervise another employee, creating a supervisor-subordinate relationship within the same entity.
Image

Binary (Degree 2)

A binary relationship involves two entity types. Binary relationships are the most common and typically represent associations between different entities.

  • Example: A Student enrolling in a Course is a binary relationship between Student and Course entities.
Image

Ternary (Degree 3)

A ternary relationship can occur in a scenario where an Employee works in a specific Department at a particular Location. The interaction among these three entities cannot be accurately represented using only binary relationships, as the information about the department and location for an employee would be lost.

Entities:

  1. Employee: Represents the individuals working in the organization.

  2. Department: Represents the functional units of the organization.

  3. Location: Represents the geographical locations where the organization operates.

Ternary Relationship: "Works In":

  • This relationship connects the Employee, Department, and Location.
  • It captures the specific connection between an employee working in a department at a particular location.
Image

Cardinality

Cardinality defines the maximum number of instances of one entity that can be associated with a single instance of another entity. Cardinality constraints include:

One-to-One (1:1)

In a one-to-one relationship, each instance of Entity A is associated with only one instance of Entity B, and vice versa.

  • Example: Each Instructor may have one Office assigned to them, and each Office can only belong to one Instructor.
Image

One-to-Many (1:N)

In a one-to-many relationship, each instance of Entity A can relate to multiple instances of Entity B, but each instance of Entity B relates to only one instance of Entity A.

  • Example: A Department can offer multiple Courses, but each Course is associated with only one Department.
Image

Many-to-One (N:1)

In a many-to-one relationship, multiple instances of Entity A can be associated with a single instance of Entity B, but each instance of Entity A relates to only one instance of Entity B.

  • Example: Multiple Students may be advised by one Instructor, but each Student has only one assigned Instructor.
Image

Many-to-Many (M:N)

In a many-to-many relationship, instances of Entity A can relate to multiple instances of Entity B, and vice versa.

  • Example: Students can enroll in multiple Courses, and each Course can have multiple Students enrolled.
Image

Participation

Participation defines whether all or only some instances of an entity are involved in a relationship. There are two types of participation:

Total Participation

In total participation, every instance of an entity must participate in the relationship. This is indicated with a double line in ER diagrams.

  • Example: If every Student must enroll in at least one Course, then Student has total participation in the "enrolls in" relationship.
Image

Partial Participation

In partial participation, only some instances of an entity participate in the relationship. This is represented by a single line in ER diagrams.

  • Example: If some Instructors do not have a dedicated Office, then Instructor has partial participation in the "assigned to" relationship with Office.
Image
Entities, Attributes, and Relationships
Attributes of Relationship Types
Mark as Completed

On this page

Degree of Relationship

Unary (Degree 1)

Binary (Degree 2)

Ternary (Degree 3)

Cardinality

One-to-One (1:1)

One-to-Many (1:N)

Many-to-One (N:1)

Many-to-Many (M:N)

Participation

Total Participation

Partial Participation